import pandas as pd

# 指定文件路径
file_path = r'D:\电信导入数据清理.xlsx'

# 读取Excel文件
with pd.ExcelFile(file_path) as xls:
    sheet3 = pd.read_excel(xls, 'Sheet3')
    sheet4 = pd.read_excel(xls, 'Sheet4')

# 将“站址编码”和“报账电表编码”列转换为字符串类型
sheet3['站址编码'] = sheet3['站址编码'].astype(str)
sheet4['报账电表编码'] = sheet4['报账电表编码'].astype(str)

# 初始化一个空列表来存储更新后的Sheet3数据
updated_sheet3 = []

# 遍历Sheet3的每一行
for index, row in sheet3.iterrows():
    # 获取当前行的站址编码
    site_code = str(row['站址编码'])
    
    # 在Sheet4中查找匹配的行
    matched_row = sheet4[sheet4['报账电表编码'] == site_code]
    
    if not matched_row.empty:
        # 获取匹配行中的能耗局站编码和能耗局站名称
        energy_code = matched_row.iloc[0]['能耗局站编码']
        energy_name = matched_row.iloc[0]['能耗局站名称']
        
        # 更新当前行并添加到列表中
        updated_row = row.copy()
        updated_row['能耗局站编码'] = energy_code
        updated_row['能耗局站名称'] = energy_name
        updated_sheet3.append(updated_row)
    else:
        # 如果没有找到匹配项，直接添加当前行，并将能耗局站编码和能耗局站名称设为空
        updated_row = row.copy()
        updated_row['能耗局站编码'] = None
        updated_row['能耗局站名称'] = None
        updated_sheet3.append(updated_row)

# 将更新后的数据转换为DataFrame
updated_sheet3_df = pd.DataFrame(updated_sheet3)

# 确保DataFrame包含所有需要的列
columns_to_include = list(sheet3.columns) + ['能耗局站编码', '能耗局站名称']
updated_sheet3_df = updated_sheet3_df[columns_to_include]

# 将站址编码列转换为字符串类型（再次确认）
updated_sheet3_df['站址编码'] = updated_sheet3_df['站址编码'].astype(str)

# 保存结果到新的Excel文件
output_file_path = r'D:\更新后的表格.xlsx'
updated_sheet3_df.to_excel(output_file_path, index=False)

print(f"数据已保存到 {output_file_path}")